package net.lgpage.db;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

/**
 * 数据库操作工具类
 * 
 * @author Longwide
 * @version 2.1
 * 
 */
public class DBUtil {

	SQLiteDatabase db;

	public DBUtil(SQLiteDatabase db) {
		super();
		this.db = db;
	}

	public DBUtil(Context context) {
		super();
		this.db = DBHelper.getDB(context);// 可以修改为自己的database
	}

	public void close() {
		if (db != null)
			db.close();
		db = null;
	}

	public interface TransExecuter {
		public void doExecute(DBUtil db);
	}
	
	public static String dropTableSql(Class clazz){
		String sql = "drop table "+tablename(clazz);
		return sql;
	}
	public static void updateTable(SQLiteDatabase db,Class clazz){
		String tablename = DBUtil.tablename(clazz);
		String sql = "select *from "+tablename+" limit 0,1";
		Cursor cr =null;
		try {
			cr = db.rawQuery(sql, null);
			System.out.println(sql);
			String cnames[] = cr.getColumnNames();
			sql = "drop table if exists "+tablename+"_tmp";
			db.execSQL(sql);
			System.out.println(sql);
			sql = "create TEMPORARY table "+tablename+"_tmp as select * from "+tablename;
			db.execSQL(sql);
			System.out.println(sql);
			sql = "drop table  if exists "+tablename;
			db.execSQL(sql);
			System.out.println(sql);
			sql = createTableSql(clazz);
			db.execSQL(sql);
			System.out.println(sql);
			sql = "select *from "+tablename+" limit 0,1";
			Field fs[] = clazz.getDeclaredFields();
			String cnames2[] = new String[fs.length];
			for(int i = 0;i<fs.length;i++){
				cnames2[i] = fs[i].getName();
			}
			System.out.println(Arrays.asList(cnames)+""+Arrays.asList(cnames2));
			StringBuilder sb = new StringBuilder();
			for(int i = 0;i<cnames.length;i++){
				for(int j = 0;j<cnames2.length;j++){
					if(cnames[i].equals(cnames2[j])){
						sb.append(cnames[i]+",");
						break;
					}
				}
			}
			sb.setLength(sb.length()-1);
			sql = "insert into "+tablename+"("+sb.toString()+") select "+sb.toString()+" from "+tablename+"_tmp";
			db.execSQL(sql);
			System.out.println(sql);
			sql = "drop table  if exists "+tablename+"_tmp";
			db.execSQL(sql);
			System.out.println(sql);
		} catch (Exception e) {
			db.execSQL(createTableSql(clazz));
		}finally{
			if(cr!=null)cr.close();
		}
	}

	public static String createTableSql(Class clazz) {
		String tablename = tablename(clazz);
		Field fs[] = clazz.getDeclaredFields();
		StringBuilder sql = new StringBuilder();
		sql.append("create table ");
		sql.append(tablename);
		sql.append(" (");
		StringBuilder sql2 = new StringBuilder();
		String pkname = pkname(clazz);
		boolean auto = auto(clazz);
		for (int i = 0; i < fs.length; i++) {
			Field f = fs[i];
			if (f.getName().equals(pkname)) {
				if (auto) {
					sql2.append(",");
					sql2.append(f.getName());
					sql2.append(" Integer primary key autoincrement");
				} else {
					sql2.append(",");
					sql2.append(f.getName());
					sql2.append(" text primary key");
				}
			} else {
				sql2.append(",");
				sql2.append(f.getName());
				sql2.append(" text");
			}
		}
		String sql2tos = sql2.substring(1);
		sql.append(sql2tos);
		sql.append(")");
		return sql.toString();
	}

	public void doExecute(TransExecuter executer) {
		db.beginTransaction();
		executer.doExecute(this);
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public <T> void saveList(final List<T> list) {
		if(isListNull(list))return;
		db.beginTransaction();
		for (int i = 0; i < list.size(); i++) {
			save(list.get(i));
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	private <T>boolean isListNull(List<T>list){
		return list==null||list.size()==0;
	}
	public <T> void updateList(List<T> list) {
		if(isListNull(list))return;
		db.beginTransaction();
		for (int i = 0; i < list.size(); i++) {
			update(list.get(i));
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public <T> void saveOrUpdateList(List<T> list) {
		if(isListNull(list))return;
		db.beginTransaction();
		for (int i = 0; i < list.size(); i++) {
			saveOrUpdate(list.get(i));
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public <T> void deleteList(List<T> list) {
		if(isListNull(list))return;
		db.beginTransaction();
		for (int i = 0; i < list.size(); i++) {
			delete(list.get(i));
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public int count(Class model, String where, String... sels) {
		StringBuilder sql = new StringBuilder();
		sql.append("select count(" + pkname(model) + ") from "
				+ tablename(model));
		if (where != null) {
			sql.append(" where ");
			sql.append(where);
		}
		return doObjQuery(sql.toString(), Integer.class, sels);
	}

	public <T> T doObjQuery(String sql, Class<T> returnClass, String... sels) {
		sql = sql + " limit 0,1";
		List<T> list = doListQuery(sql, returnClass, sels);
		return (list == null || list.size() == 0) ? null : list.get(0);
	}

	public <T> List<T> doListQuery(String sql, Class<T> returnClass,
			String... sels) {
		Cursor cr = db.rawQuery(sql, sels);
		List<T> list = cursorToList(cr, returnClass);
		return list;
	}

	public <T> T getObj(Class<T> returnClass, String where, String... sels) {
		List<T> list = getPageList(1, 1, returnClass, where, sels);
		return (list == null || list.size() == 0) ? null : list.get(0);
	}

	public <T> List<T> getPageList(Integer pageno, Integer pagemax,
			Class<T> returnClass, String where, String... sels) {
		StringBuilder sql = new StringBuilder(where);
		if ((pageno != null) && (pagemax != null)) {
			sql.append(" limit " + (pageno.intValue() - 1) * pagemax.intValue()
					+ "," + pagemax);
		}
		Cursor cr = db.query(tablename(returnClass), columns(returnClass),
				sql.toString(), sels, null, null, null);
		List<T> list = cursorToList(cr, returnClass);
		return list;
	}

	public <T> List<T> getList(Class<T> returnClass, String where,
			String... sels) {
		Cursor cr = db.query(tablename(returnClass), columns(returnClass),
				where, sels, null, null, null);
		List<T> list = cursorToList(cr, returnClass);
		return list;
	}

	public <T> List<T> findAll(Class<T> returnClass) {
		Cursor cr = db.query(tablename(returnClass), columns(returnClass),
				null, null, null, null, null);
		List<T> list = cursorToList(cr, returnClass);
		return list;
	}

	public <T> List<T> findAll(Class tableClass,Class<T> returnClass) {
		Cursor cr = db.query(tablename(tableClass), columns(tableClass),
				null, null, null, null, null);
		List<T> list = cursorToList(cr, returnClass);
		return list;
	}
	public boolean deleteTable(Class model) {
		int count = db.delete(tablename(model), null, null);
		return count != -1;
	}
	
	public boolean delete(Object obj,Class tableClass){
		String id = pkname(tableClass);
		int count = db.delete(tablename(tableClass), id + " = ?", pkobj(obj));
		return count != -1;
	}

	public boolean delete(Object obj) {
		Class model = obj.getClass();
		String id = pkname(model);
		int count = db.delete(tablename(model), id + " = ?", pkobj(obj));
		return count != -1;
	}

	public int delete(Class model, String where, String... sels) {
		return db.delete(tablename(model), where, sels);
	}

	public boolean save(Object obj) {
		return save(obj, obj.getClass());
	/*	ContentValues values = values(obj);
		Class model = obj.getClass();
		boolean auto = auto(model);
		String nullclouwn = null;
		long count = -1;
		if (auto) {
			values.remove(pkname(model));
			nullclouwn = pkname(model);
		} else {
		}
		count = db.insert(tablename(model), nullclouwn, values);
		return count != -1;*/
	}
	public boolean save(Object obj,Class tableClass) {
		ContentValues values = values(obj);
		Class model = obj.getClass();
		boolean auto = auto(model);
		String nullclouwn = null;
		long count = -1;
		if (auto) {
			values.remove(pkname(model));
			nullclouwn = pkname(model);
		} else {
		}
		count = db.insert(tablename(tableClass), nullclouwn, values);
		return count != -1;
	}
	public boolean saveOrUpdate(Object obj) {
		ContentValues values = values(obj);
		Class model = obj.getClass();
		boolean auto = auto(model);
		String nullclouwn = null;
		long count = -1;
		if (auto) {
			values.remove(pkname(model));
			nullclouwn = pkname(model);
			count = db.insert(tablename(model), nullclouwn, values);
		} else {
			try {
				count = db.insertOrThrow(tablename(model), nullclouwn, values);
			} catch (SQLException e) {
				update(obj);
			}
		}
		return count != -1;
	}

	public long update(Object obj, String[] fields, String where,
			String... sels) {
		ContentValues values = values(obj, fields);
		Class model = obj.getClass();
		long count = db.update(tablename(model), values, where, sels);
		return count;
	}

	public long update(Object obj) {
		Class model = obj.getClass();
		String id = pkname(model);
		ContentValues values = values(obj);
		long count = db.update(tablename(model), values, id + " = ?",
				pkobj(obj));
		return count;
	}

	public static String tablename(Class clazz) {
		TableName name = (TableName) clazz.getAnnotation(TableName.class);
		if (name == null) {
			return clazz.getSimpleName().toLowerCase();
		} else {
			return name.value();
		}
	}

	public static String pkname(Class clazz) {
		/*return clazz.getDeclaredFields()[clazz.getDeclaredFields().length - 1]
				.getName();*/
		PKName name = (PKName) clazz.getAnnotation(PKName.class);
		if(name == null){
			String pkname =  clazz.getDeclaredFields()[0]
					.getName();
			if(pkname.endsWith("id")){
				return pkname;
			}
			pkname = clazz.getDeclaredFields()[clazz.getDeclaredFields().length-1].getName();
			return pkname;
		}else{
			return name.value();
		}
		
	}

	public static boolean auto(Class clazz) {
		AutoIncrement auto = (AutoIncrement) clazz
				.getAnnotation(AutoIncrement.class);
		return auto != null;
	}

	public static String[] columns(Class model) {
		if(model.getSuperclass()!=Object.class){
			Field fs[] = model.getDeclaredFields();
			Field fs2[] = model.getSuperclass().getDeclaredFields();
			String sels[] = new String[fs.length+fs2.length];
			for (int i = 0; i < fs.length; i++) {
				sels[i] = fs[i].getName();
			}
			for (int i = 0; i < fs2.length; i++) {
				sels[i+fs.length] = fs2[i].getName();
			}
			return sels;
		}else{
			Field fs[] = model.getDeclaredFields();
			String sels[] = new String[fs.length];
			for (int i = 0; i < fs.length; i++) {
				sels[i] = fs[i].getName();
			}
			return sels;
		}
	}

	public static ContentValues values(Object obj) {
		Class model = obj.getClass();
		Field fs[] = model.getDeclaredFields();
		ContentValues values = new ContentValues();
		for (int i = 0; i < fs.length; i++) {
			try {
				fs[i].setAccessible(true);
				Object v = fs[i].get(obj);
				if(v instanceof Date){
					Date d = (Date) v;
					values.put(fs[i].getName(), d.getTime());
				}else{
					String vs = v==null? null:v.toString();
					values.put(fs[i].getName(), vs);
				}
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			}
		}
		return values;
	}

	public static ContentValues values(Object obj, String... fields) {
		ContentValues values = new ContentValues();
		Class model = obj.getClass();
		for (int i = 0; i < fields.length; i++) {
			try {
				Field fd = model.getDeclaredField(fields[i]);
				fd.setAccessible(true);
				Object v = fd.get(obj);
				if(v instanceof Date){
					Date d = (Date) v;
					values.put(fd.getName(), d.getTime());
				}else{
					String vs = v==null? null:v.toString();
					values.put(fd.getName(), vs);
				}
			} catch (NoSuchFieldException e) {
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			}
		}
		return values;
	}

	public static String[] pkobj(Object obj) {
		try {
			Field pkfd = obj.getClass()
					.getDeclaredField(pkname(obj.getClass()));
			pkfd.setAccessible(true);
			Object result = pkfd.get(obj);
			if (result == null) {
				return null;
			}
			String ss[] = { result.toString() };
			return ss;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		}
		return null;
	}
	private static void cursorToObj(Cursor cr,Field fd,Object obj){
		Object o = null;
		Class type  = fd.getType();
		if (type == int.class || type == Integer.class) {
			o = cr.getInt(cr.getColumnIndex(fd.getName()));
		} else if (type == String.class) {
			o = cr.getString(cr.getColumnIndex(fd.getName()));
		} else if (type == Double.class || type == double.class) {
			o = cr.getDouble(cr.getColumnIndex(fd.getName()));
		} else if (type == Float.class || type == float.class) {
			o = cr.getFloat(cr.getColumnIndex(fd.getName()));
		} else if (type == Long.class || type == long.class) {
			o = cr.getLong(cr.getColumnIndex(fd.getName()));
		}else if(type == Date.class){
			Long l = cr.getLong(cr.getColumnIndex(fd.getName()));
			System.out.println(l);
			o = l==null||l==0?null:new Date(l);
		}else{
			Class c = (Class) type;
			try {
				o = c.newInstance();
				Field[]fs = c.getDeclaredFields();
				for(Field f:fs){
					cursorToObj(cr,f, o);
				}
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		fd.setAccessible(true);
		try {
			fd.set(obj, o);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
	}

	private static <T> T cursorToObj(Cursor cr, int i, Type type) {
		Object o = null;
		if (type == int.class || type == Integer.class) {
			o = cr.getInt(i);
		} else if (type == String.class) {
			o = cr.getString(i);
		} else if (type == Double.class || type == double.class) {
			o = cr.getDouble(i);
		} else if (type == Float.class || type == float.class) {
			o = cr.getFloat(i);
		} else if (type == Long.class || type == long.class) {
			o = cr.getLong(i);
		}else if(type == Date.class){
			Long l = cr.getLong(i);
			o = l==null||l==0?null:new Date(l);
		}else{
			
		}
		return (T) o;
	}

	public static <T> List<T> cursorToList(Cursor cr, Class model) {
		List<T> list = new ArrayList<T>();
		if (model == String.class || model == Integer.class
				|| model == Long.class || model == Float.class
				|| model == Double.class || model == Short.class) {
			try {
				while (cr.moveToNext()) {
					list.add((T) cursorToObj(cr, 0, model));
					// list.add((T) rs.getObject(1));
					// m = (T) rs.getObject(1);
				}
			} catch (SQLException e) {// rs被关闭或者getObject没有了 不作处理

			}
		} else {
			Constructor<T> cs = null;
			try {
				cs = model.getConstructor();
			} catch (SecurityException e2) {
				e2.printStackTrace();
			} catch (NoSuchMethodException e2) {
				e2.printStackTrace();
			}
			Field fs[] = model.getDeclaredFields();
			while (cr.moveToNext()) {
				T m = null;
				try {
					m = cs.newInstance();
				} catch (IllegalArgumentException e1) {
					e1.printStackTrace();
				} catch (InstantiationException e1) {
					e1.printStackTrace();
				} catch (IllegalAccessException e1) {
					e1.printStackTrace();
				} catch (InvocationTargetException e1) {
					e1.printStackTrace();
				}
				for (int i = 0; i < fs.length; i++) {
					cursorToObj(cr,fs[i],m);
				}
				if (m != null)
					list.add(m);
			}
		}
		cr.close();
		return list;
	}
	@Retention(RetentionPolicy.RUNTIME)
	@Target(ElementType.TYPE)
	public @interface AutoIncrement {
		
	}
	@Retention(RetentionPolicy.RUNTIME)
	@Target(ElementType.TYPE)
	public @interface TableName {
		String value();
	}
	@Retention(RetentionPolicy.RUNTIME)
	@Target(ElementType.TYPE)
	public @interface PKName {
		String value();
	}
}
